package com.ascent.bean.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.ascent.bean.Orderitem;
import com.ascent.bean.Orders;
import com.ascent.bean.Product;
import com.ascent.bean.Productuser;
import com.ascent.util.DataAccess;

public class OrderDAO {
    /**
     * 保存订单方法
     *
     * @param con 包含订单中商品信息的集合，元素类型为Product
     * @param pu  包含用户信息的Productuser对象，用于获取用户ID
     * @return 包含订单商品信息的HTML表格片段字符串，用于前端展示订单详情
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public String SaveOrder(Collection con, Productuser pu) throws SQLException {
        Connection conn = (Connection) DataAccess.getConnection();
        Statement st;
        String content = ""; // 初始化订单内容字符串
        int uid = pu.getUid();

        // 未注册用户处理逻辑
        if (uid == 0) {
            Iterator it = con.iterator();
            while (it.hasNext()) {
                Product p = (Product) it.next();
                // 构建HTML表格行，用于展示商品信息
                String name = p.getProductname();
                String catalog = p.getCatalogno();
                String cas = p.getCas();
                String formula = p.getCategory();
                String mw = p.getPrice1();
                String quantity = p.getQuantity();
                String temp = "<tr  bgcolor='#99CCFF'> <td width='71' class='table_hei'>" + catalog + "</td><td width='100' class='table_hei'>" + name + "</td><td width='107' class='table_hei'>" + cas + "</td><td width='64' class='table_hei'>" + formula + "</td><td  width='82' class='table_hui'>" + mw + " ��</td><td width='59' height='26'  class='table_hui'>" + quantity + "</td></tr>";
                content += temp;
            }
        } else { // 注册用户处理逻辑
            Orders orders = new Orders(); // 创建订单对象
            orders.setUid(pu.getUid()); // 设置订单的用户ID

            // 获取当前时间作为订单时间
            Date currentTime = new Date();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateString = formatter.format(currentTime);

            // 插入订单记录到orders表
            st = conn.createStatement();
            String sql = "insert into orders (uid,datetime,delsoft) values (" + pu.getUid() + ",'" + dateString + "','" + 1 + "')";
            st.executeUpdate(sql);

            // 查询刚插入的订单ID
            String sqls = "select * from orders o where o.uid=" + pu.getUid() + " and o.datetime='" + dateString + "'";
            ResultSet rs = st.executeQuery(sqls);
            int orderid = 0;
            while (rs.next()) {
                orderid = rs.getInt("orderid");// 获取订单ID
            }
            st.close();

            // 插入订单项记录到orderitem表
            if (orderid != 0) {
                Iterator it = con.iterator();
                while (it.hasNext()) {
                    Product p = (Product) it.next();
                    st = conn.createStatement();
                    String sqlor = "insert into orderitem (orderid,pid,quantity) values (" + orderid + ",'" + p.getPid() + "','" + p.getQuantity() + "')";
                    st.executeUpdate(sqlor);

                    // 构建HTML表格行，用于展示商品信息
                    String name = p.getProductname();
                    String catalog = p.getCatalogno();
                    String cas = p.getProductname();
                    String formula = p.getCategory();
                    String mw = p.getPrice1();
                    String quantity = p.getQuantity();
                    String temp = "<tr  bgcolor='#99CCFF'> <td width='71' class='table_hei'>" + catalog + "</td><td width='100' class='table_hei'>" + cas + "</td><td width='107' class='table_hei'>" + name + "</td><td width='64' class='table_hei'>" + formula + "</td><td  width='82' class='table_hui'>" + mw + " ��</td><td width='59' height='26'  class='table_hui'>" + quantity + "</td></tr>";
                    content += temp;
                }
                st.close();
                conn.close();
            } else {
                System.out.println("订单插入失败");
            }
        }
        return content;
    }

    /**
     * 查询指定用户的订单列表
     *
     * @param uid 用户ID，用于查询该用户的订单
     * @return 包含Orders对象的列表，每个Orders对象对应一个订单信息
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public List OrderList(int uid) throws SQLException {
        List list = new ArrayList();
        Connection con = DataAccess.getConnection();
        Statement st;
        // 查询指定用户且未被软删除的订单，按订单ID倒序排列
        String sql = "select * from orders o where o.uid=" + uid + " and o.delsoft=1 order by o.orderid desc";
        st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            Orders od = new Orders();
            od.setOrderid(rs.getInt("orderid"));
            od.setUid(rs.getInt("uid"));
            od.setDatetime(rs.getString("datetime"));
            list.add(od);
        }
        rs.close();
        st.close();
        con.close();
        return list;
    }

    /**
     * 查询所有未被软删除的订单列表
     *
     * @return 包含Orders对象的列表，每个Orders对象对应一个订单信息
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public List OrderAllList() throws SQLException {
        List list = new ArrayList();
        Connection con = DataAccess.getConnection();
        Statement st;
        // 查询所有未被软删除的订单，按订单ID倒序排列
        String sql = "select * from orders o where o.delsoft=1 order by o.orderid desc";
        st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            Orders od = new Orders();
            od.setOrderid(rs.getInt("orderid"));
            od.setUid(rs.getInt("uid"));
            od.setDatetime(rs.getString("datetime"));
            list.add(od);
        }
        rs.close();
        st.close();
        con.close();
        return list;
    }

    /**
     * 查询指定订单的订单项列表
     *
     * @param orderid 订单ID，用于查询该订单下的订单项
     * @return 包含Product对象的列表，每个Product对象对应一个订单项的商品信息
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public List OrderItem(int orderid) throws SQLException {
        List list = new ArrayList();
        Connection con = DataAccess.getConnection();
        Statement st;
        // 通过子查询获取指定订单的订单项对应的商品信息
        String sql = "select * from product where pid in(select pid from orderitem o where o.orderid=" + orderid + ")";
        st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            Product pu = new Product();
            pu.setPid(rs.getInt("pid"));
            pu.setProductId(rs.getString("product_id"));
            pu.setProductname(rs.getString("productname"));
            pu.setCatalogno(rs.getString("catalogno"));
            pu.setCas(rs.getString("cas"));
            pu.setFormula(rs.getString("formula"));
            pu.setMdlnumber(rs.getString("mdlnumber"));
            pu.setMw(rs.getString("mw"));
            pu.setNewproduct(rs.getString("newproduct"));
            pu.setStructure(rs.getString("structure"));
            pu.setRealstock(rs.getString("realstock"));
            pu.setPrice1(rs.getString("price1"));
            pu.setPrice2(rs.getString("price2"));
            pu.setCategory(rs.getString("category"));
            pu.setNote(rs.getString("note"));
            pu.setStock(rs.getString("stock"));
            pu.setNote(rs.getString("note"));
            pu.setDelFlag(rs.getInt("del_flag"));
            list.add(pu);
        }
        rs.close();
        st.close();
        con.close();
        return list;
    }

    /**
     * 删除指定订单的订单项信息
     *
     * @param pid    订单项对应的商品ID
     * @param orderid 订单ID
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public void delOrderItem(int pid, int orderid) throws SQLException {
        Connection con = DataAccess.getConnection();
        Statement st;
        // 构建删除订单项的SQL语句
        String sql = "delete from orderitem  where orderid=" + orderid + " and pid =" + pid;
        st = con.createStatement();
        st.executeUpdate(sql);
        st.close();
        con.close();
    }

    /**
     * 硬删除订单及其相关订单项信息
     *
     * @param orderid 订单ID
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public void delOrder(int orderid) throws SQLException {
        Connection con = DataAccess.getConnection();
        Statement st;
        // 构建删除订单项和订单的SQL语句
        String sql = "delete from orders  where orderid=" + orderid;
        String sqls = "delete from orderitem  where orderid=" + orderid;
        st = con.createStatement();
        st.executeUpdate(sqls); // 删除订单相关的订单项
        st.executeUpdate(sql); // 删除订单
        st.close();
        con.close();
    }

    /**
     * 软删除订单，将订单的delsoft字段设置为0
     *
     * @param orderid 订单ID
     * @throws SQLException 当数据库操作出现异常时抛出
     */
    public void delOrdersoft(int orderid) throws SQLException {
        Connection con = DataAccess.getConnection();
        Statement st;
        // 构建软删除订单的SQL语句
        String sql = "update orders set delsoft='0' where orderid=" + orderid;
        st = con.createStatement();
        st.execute(sql);
        st.close();
        con.close();
    }
}